options compress=YES;

libname original ''; 
libname fra '';

* APPEND SEGMENTS AND COLLAPSE INTO COHORTS ;

***********************************************************;
* APPEND ALL SEGMENTS ;
* *********************************************************;

data mbrphusser_all;
	set 
		fra.mbrphusser02
		fra.mbrphusser03
		fra.mbrphusser05
		fra.mbrphusser06
		fra.mbrphusser09
		fra.mbrphusser10
		fra.mbrphusser15
		fra.mbrphusser16
		fra.mbrphusser19
		fra.mbrphusser20
		;

run;

***********************************************************;
* COLLAPSE BY COHORT AND RETIREMENT/CLAIMING YEAR AND MERGE;
* *********************************************************;

* TWO SETS OF FILES: RESTRICT TO 10+ YEARS OF EARNINGS, THOSE WORKING AT 55;
data mbrphusser_earn10yr;
	set mbrphusser_all (where=(n_earnyrnum60>=10));
run;

data mbrphusser_earn55;
	set mbrphusser_all (where=(n_earnat55=1));
run;

%macro collapse(set);

* Summary stats ;
data mbrphusser_&set._summstats ;
	set mbrphusser_&set. 
		(keep= ssn ser_dobyy n_rflow_: n_hzlast_: n_hzfirst_:
		ser_race ser_sex n_earnyrnum60 n_earnavg25to55 n_earndelt55to60
		n_retire_age: n_claim_age: n_diff: restat_num_rflow
		) 
	;

	if 		ser_dobyy >= 1935 & ser_dobyy <= 1937 then coh = 1 ; 	
	else if ser_dobyy >= 1938 & ser_dobyy <= 1942 then coh = 2 ; 	
	else if ser_dobyy >= 1943 & ser_dobyy <= 1945 then coh = 3 ; 

	* Cohort characteristics ;
	if ser_sex="M" then n_male=1;
		else if ser_sex="F" then n_male=0;
		else n_male=.;

	%macro race(uc,lc);
	if ser_race="&uc." then n_race_&lc.=1;
		else n_race_&lc.=0;
	%mend race;
	%race(A,a);
	%race(B,b);
	%race(H,h);
	%race(W,w);
	if (ser_race="I" | ser_race="O" | ser_race="U") then n_race_o=1;
		else n_race_o=0;

run;

proc rank data=mbrphusser_&set._summstats out=mbrphusser_&set._summstats groups=2;
	var n_earnavg25to55;
	ranks n_earnavgmed;
run;

proc means noprint data=mbrphusser_&set._summstats nway;
	class coh ;
	var ser_dobyy 
		n_male n_race_w n_race_h n_race_b n_race_a n_race_o 
		n_earnyrnum60 n_earnavg25to55 n_earndelt55to60 
		n_retire_age: n_claim_age: n_diff: restat_num_rflow ;
	output out=fra.summstats_&set.
	N(ser_dobyy)=count 
	MEAN(n_male n_race_w n_race_h n_race_b n_race_a n_race_o 
		n_earnyrnum60 n_earnavg25to55 n_earndelt55to60
		n_retire_age: n_claim_age: n_diff: restat_num_rflow
		)=
	;
run;

proc means noprint data=mbrphusser_&set._summstats nway;
	class coh n_male ;
	var ser_dobyy 
		n_male n_race_w n_race_h n_race_b n_race_a n_race_o 
		n_earnyrnum60 n_earnavg25to55 n_earndelt55to60 
		n_retire_age: n_claim_age: n_diff: restat_num_rflow ;
	output out=fra.summstats_&set._bymale
	N(ser_dobyy)=count 
	MEAN(n_male n_race_w n_race_h n_race_b n_race_a n_race_o 
		n_earnyrnum60 n_earnavg25to55 n_earndelt55to60
		n_retire_age: n_claim_age: n_diff: restat_num_rflow
		)=
	;
run;

proc means noprint data=mbrphusser_&set._summstats nway;
	class coh n_earnavgmed ;
	var ser_dobyy 
		n_male n_race_w n_race_h n_race_b n_race_a n_race_o 
		n_earnyrnum60 n_earnavg25to55 n_earndelt55to60 
		n_retire_age: n_claim_age: n_diff: restat_num_rflow ;
	output out=fra.summstats_&set._byearn
	N(ser_dobyy)=count 
	MEAN(n_male n_race_w n_race_h n_race_b n_race_a n_race_o 
		n_earnyrnum60 n_earnavg25to55 n_earndelt55to60
		n_retire_age: n_claim_age: n_diff: restat_num_rflow
		)=
	;
run;


* Reshape data long ;
data mbrphusser_&set._long;
	set mbrphusser_&set. 
		(keep= ssn ser_dobyy n_rflow_: n_hzlast_: n_hzfirst_:
		ser_race ser_sex n_earnyrnum60 n_earnavg25to55 n_earndelt55to60) 
	;

	array	arflow(1951:2013)	n_rflow_1951-n_rflow_2013;
	array	ahzlast(1951:2013)	n_hzlast_1951-n_hzlast_2013;
	array	ahzfirst(1951:2013)	n_hzfirst_1951-n_hzfirst_2013;
	
	do year = 1951 to 2013;
		n_rflow = arflow(year);
		n_hzlast = ahzlast(year);
		n_hzfirst = ahzfirst(year);
		output;
	end;

	drop n_rflow_1951-n_rflow_2013;	
	drop n_hzlast_1951-n_hzlast_2013;	
	drop n_hzfirst_1951-n_hzfirst_2013;	
run;

* LF exit flow ;
data collapse_lfexitflow_&set.;
	set mbrphusser_&set._long (where=(n_rflow=1));
	* Cohort characteristics ;
	if ser_sex="M" then n_male=1;
		else if ser_sex="F" then n_male=0;
		else n_male=.;

	%macro race(uc,lc);
	if ser_race="&uc." then n_race_&lc.=1;
		else n_race_&lc.=0;
	%mend race;
	%race(A,a);
	%race(B,b);
	%race(H,h);
	%race(W,w);
	if (ser_race="I" | ser_race="O" | ser_race="U") then n_race_o=1;
		else n_race_o=0;

run;

proc means noprint data=collapse_lfexitflow_&set. nway;
	class ser_dobyy year;
	var n_rflow n_male n_race_w n_race_h n_race_b n_race_a n_race_o 
		n_earnyrnum60 n_earnavg25to55 n_earndelt55to60 ;
	output out=collapse_lfexitflow_&set.
	N(n_rflow)=&set._lfexit_flow 
	MEAN(n_male n_race_w n_race_h n_race_b n_race_a n_race_o 
		n_earnyrnum60 n_earnavg25to55 n_earndelt55to60)=	
		&set._male &set._race_w &set._race_h &set._race_b &set._race_a &set._race_o 
		&set._earnyrnum60 &set._earnavg25to55 &set._earndelt55to60
	;
run;

* Hazard variables ;
proc means noprint data=mbrphusser_&set._long nway;
	class ser_dobyy year;
	var n_rflow n_hzlast n_hzfirst ;
	output out=collapse_hazard_&set.
	MEAN(n_rflow n_hzlast n_hzfirst)=
		&set._lfexitflow_frac &set._hzlast_frac &set._hzfirst_frac
	;
run;

* Single retirement year ;
proc means noprint data=mbrphusser_&set. nway;
	class ser_dobyy n_retire_yr ;
	var ser_dob restat_num_rflow ;
	output out=collapse_lfexitone_&set.
	N(ser_dob)=&set._lfexit_one
	MEAN(restat_num_rflow)=&set._restat_num_rflow 
	;
run;

proc means noprint data=mbrphusser_&set. nway;
	class ser_dobyy n_retire_yr_1;
	var ser_dob ;
	output out=collapse_lfexitone_1_&set.
	N(ser_dob)=&set._lfexit_one_1
	;
run;

proc means noprint data=mbrphusser_&set. nway;
	class ser_dobyy n_retire_yr_rbst ;
	var ser_dob ;
	output out=collapse_lfexitone_rbst_&set.
	N(ser_dob)=&set._lfexit_one_rbst
	;
run;

* PHUS claiming year ;
proc means noprint data=mbrphusser_&set. nway;
	class ser_dobyy n_phus1old_yr;
	var ser_dob;
	output out=collapse_phusold_&set.
	N(ser_dob)=&set._claim_phusold;
run;

proc means noprint data=mbrphusser_&set. nway;
	class ser_dobyy n_phus1any_yr;
	var ser_dob;
	output out=collapse_phusany_&set.
	N(ser_dob)=&set._claim_phusany;
run;

* MBA claiming year ;
proc means noprint data=mbrphusser_&set. nway;
	class ser_dobyy n_mba1old_yr;
	var ser_dob;
	output out=collapse_mbaold_&set.
	N(ser_dob)=&set._claim_mbaold;
run;

proc means noprint data=mbrphusser_&set. nway;
	class ser_dobyy n_mba1any_yr;
	var ser_dob;
	output out=collapse_mbaany_&set.
	N(ser_dob)=&set._claim_mbaany;
run;

%mend collapse;

%collapse(earn10yr);
%collapse(earn55);

* Merge all together;
data fra.collapse_combine;

merge 
	
	collapse_lfexitflow_earn10yr
	(keep=ser_dobyy year earn10yr_lfexit_flow 
		earn10yr_male earn10yr_race_w earn10yr_race_h earn10yr_race_b earn10yr_race_a earn10yr_race_o
		earn10yr_earnyrnum60 earn10yr_earnavg25to55 earn10yr_earndelt55to60)

	collapse_hazard_earn10yr
	(keep=ser_dobyy year earn10yr_lfexitflow_frac earn10yr_hzlast_frac earn10yr_hzfirst_frac)

	collapse_lfexitone_earn10yr
	(keep=ser_dobyy n_retire_yr earn10yr_lfexit_one earn10yr_restat_num_rflow
	rename=(n_retire_yr=year))

	collapse_lfexitone_1_earn10yr
	(keep=ser_dobyy n_retire_yr_1 earn10yr_lfexit_one_1
	rename=(n_retire_yr_1=year))

	collapse_lfexitone_rbst_earn10yr
	(keep=ser_dobyy n_retire_yr_rbst earn10yr_lfexit_one_rbst
	rename=(n_retire_yr_rbst=year))

	collapse_phusold_earn10yr
	(keep=ser_dobyy n_phus1old_yr earn10yr_claim_phusold
	rename=(n_phus1old_yr=year))

	collapse_phusany_earn10yr
	(keep=ser_dobyy n_phus1any_yr earn10yr_claim_phusany
	rename=(n_phus1any_yr=year))

	collapse_mbaold_earn10yr
	(keep=ser_dobyy n_mba1old_yr earn10yr_claim_mbaold
	rename=(n_mba1old_yr=year))

	collapse_mbaany_earn10yr
	(keep=ser_dobyy n_mba1any_yr earn10yr_claim_mbaany
	rename=(n_mba1any_yr=year))

	collapse_lfexitflow_earn55
	(keep=ser_dobyy year earn55_lfexit_flow
		earn55_male earn55_race_w earn55_race_h earn55_race_b earn55_race_a earn55_race_o
		earn55_earnyrnum60 earn55_earnavg25to55 earn55_earndelt55to60)

	collapse_hazard_earn55
	(keep=ser_dobyy year earn55_lfexitflow_frac earn55_hzlast_frac earn55_hzfirst_frac)

	collapse_lfexitone_earn55
	(keep=ser_dobyy n_retire_yr earn55_lfexit_one earn55_restat_num_rflow
	rename=(n_retire_yr=year))

	collapse_lfexitone_1_earn55
	(keep=ser_dobyy n_retire_yr_1 earn55_lfexit_one_1
	rename=(n_retire_yr_1=year))

	collapse_lfexitone_rbst_earn55
	(keep=ser_dobyy n_retire_yr_rbst earn55_lfexit_one_rbst
	rename=(n_retire_yr_rbst=year))

	collapse_phusold_earn55
	(keep=ser_dobyy n_phus1old_yr earn55_claim_phusold
	rename=(n_phus1old_yr=year))

	collapse_phusany_earn55
	(keep=ser_dobyy n_phus1any_yr earn55_claim_phusany
	rename=(n_phus1any_yr=year))

	collapse_mbaold_earn55
	(keep=ser_dobyy n_mba1old_yr earn55_claim_mbaold
	rename=(n_mba1old_yr=year))

	collapse_mbaany_earn55
	(keep=ser_dobyy n_mba1any_yr earn55_claim_mbaany
	rename=(n_mba1any_yr=year))
	;

	by ser_dobyy year;
run;

* Create a data set for cumulative variables;
proc means noprint data=mbrphusser_earn10yr nway;
	class ser_dobyy;
	var n_anyearn: n_phusanyyr: ;
	output out=fra.collapse_cumlf_earn10yr
	MEAN(n_anyearn: n_phusanyyr: )=;
run;

proc means noprint data=mbrphusser_earn55 nway;
	class ser_dobyy;
	var n_anyearn: n_phusanyyr: ;
	output out=fra.collapse_cumlf_earn55
	MEAN(n_anyearn: n_phusanyyr:)=;
run;

* CONVERT DATA SETS TO STATA;
%let DIR=;
proc export data=fra.summstats_earn10yr
     file="&DIR\summstats_earn10yr.dta"
     dbms=DTA REPLACE;
run;
proc export data=fra.summstats_earn10yr_bymale
     file="&DIR\summstats_earn10yr_bymale.dta"
     dbms=DTA REPLACE;
run;
proc export data=fra.summstats_earn10yr_byearn
     file="&DIR\summstats_earn10yr_byearn.dta"
     dbms=DTA REPLACE;
run;

proc export data=fra.summstats_earn55
     file="&DIR\summstats_earn55.dta"
     dbms=DTA REPLACE;
run;
proc export data=fra.collapse_combine
     file="&DIR\collapse_combine.dta"
     dbms=DTA REPLACE;
run;
proc export data=fra.collapse_cumlf_earn10yr
     file="&DIR\collapse_cumlf_earn10yr.dta"
     dbms=DTA REPLACE;
run;
proc export data=fra.collapse_cumlf_earn55
     file="&DIR\collapse_cumlf_earn55.dta"
     dbms=DTA REPLACE;
run;

quit;


